1. Field of the Invention
This invention relates in general to computer-implemented database systems, and, in articular, to a technique for ensuring cache coherency for spawned dependent transactions in multi-system environment with shared data storage devices.
2. Description of Related Art
Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database management system (DBMS) which uses relational techniques for storing and retrieving data. Relational databases are organized into tables which consist of rows and columns of data. The rows are formally called tuples or records. A database will typically have many tables and each table will typically have multiple tuples and multiple columns. The tables are typically stored on direct access storage devices (DASD), such as magnetic or optical disk drives for semi-permanent storage.
In a DBMS, data records are stored in table spaces. A table space may contain one or more tables. Each table space contains a number of space map pages. Each space map page covers a number of data pages. One or more records can be stored in a single data page. All data pages within a single table space must have the same page size. Typically, a page contains 4096 bytes.
In a multi-system environment with shared data storage devices, multiple computers are connected by a network to each other and to shared data storage devices (e.g., disks). In this system, the independently operating computers use storage consisting of one or more direct access storage devices (DASD""s). Each computer system includes a database management system (DBMS) which provides access to databases stored on the DASD-oriented external storage subsystem. Additionally, each computer may share a cache in the form of a high-speed, frequently accessed shared electronic store (SES). When a SES is used, a computer will retrieve data from a DASD, process the data, and copy the data to the SES. Therefore, the SES may have a version of data that is more recent than the version stored on the DASD subsystem.
Additionally, each computer has storage consisting of a local buffer pool. In this context, each DBMS possesses and maintains, in its own internal storage, a local buffer pool to which data is fetched for access by one or more locally-executing applications. Each DBMS further includes a buffer manager (BM) for allocating buffer space and for controlling references to it. The buffer manager has access to tables, lists, and other structures containing information about the buffer. The buffer manager coordinates the movement of these structures between its buffer and external storage via the SES and/or DASD.
A DBMS obtains data and places it in its local buffer in response to transactional activity generated by applications executing on the DBMS""s processor. Such applications generate read and write operations which, in turn, utilize the local buffer for access to the data. Furthermore, in the multi-system transaction environment, locking may be used by the computers to gain exclusive access to the data.
In a multi-system environment with shared data storage devices containing what will be referred to as xe2x80x9coriginalxe2x80x9d shared data, if there are two DBMS images called DB2A and DB2B that are sharing data, each will have a copy of the original shared data in a local buffer pool (e.g., a local cache). One could be modifying its copy of the shared data in its local buffer pool. This may lead to one of the DBMS images working with a version of the shared data in its local buffer pool that does not contain the modifications made by the other DBMS image in its own local buffer pool. In cases in which a DBMS image is dependent on the data modified by another DBMS, this could lead to problems.
For example, assume that there is a transaction, Transaction-1, that runs on DB2A and that makes an update to page P1 in DB2A""s local buffer pool. Now, before Transaction-1 commits, it spawns a second transaction, Transaction-2, that is dependent on the update to P1 that was made by Transaction-1. For example, Transaction-1 may insert a record into a table as a way of passing information (e.g., parameters) to Transaction-2. In this scenario, Transaction-2 is called the xe2x80x9cspawned dependent transaction,xe2x80x9d and Transaction-1 is called the xe2x80x9coriginating transaction.xe2x80x9d If Transaction-2 runs on the same DBMS image as Transaction-1, then Transaction-2 and Transaction-1 share access to the same local buffer pool memory, and thus Transaction-2 can xe2x80x9cseexe2x80x9d Transaction-1""s still-uncommitted changes to Transaction-1.
When Transaction-2 sees the qualifying row on page P1, Transaction-2 requests a lock on the row or page which causes Transaction-2 to wait for Transaction-1 to commit, and then Transaction-2 gets the lock and sees the committed update from Transaction-1. However, if Transaction-2 runs on a different DBMS image than Transaction-1, then Transaction-2 and Transaction-1 do not share access to the same local buffer pool (DB2A and DB2B each have their own local buffer pool storage), and, therefore, Transaction-2 may see a down-level (i.e., local or downloaded from a shared data storage device) copy of page P1 that does not yet reflect Transaction-1""s still-uncommitted update to P1. That is, Transaction-2 has a xe2x80x9cstalexe2x80x9d copy of data (i.e., data that does not include updates made by Transaction-1) in its local buffer pool. So when Transaction-2 evaluates the rows in DB2B""s down-level copy of P1, Transaction-2 may not see Transaction-1""s still-uncommitted update to P1, and, therefore, may not find the qualifying row from Transaction-1. So Transaction-2 gets a xe2x80x9crecord not foundxe2x80x9d return code instead of finding the row and waiting for Transaction-1 to commit.
Therefore, there is a need in the art for an efficient technique for ensuring cache coherency for spawned dependent transactions in a multi-system environment with shared data storage devices.
To overcome the limitations in the prior art described above, and to overcome other limitations that will become apparent upon reading and understanding the present specification, the present invention discloses a method, apparatus, and article of manufacture for a computer-implemented technique for ensuring cache coherency for spawned dependent transactions in a multi-system environment with shared data storage devices.
In accordance with the present invention, a cache coherency system ensures cache coherency in a database containing data stored on a data storage device connected to a plurality of computers. At a first computer, a first transaction updates data in a local buffer pool. When an immediate write option is set, the data in the local buffer pool of the first computer is immediately written to a group buffer pool at a central system. Then, a local buffer pool invalidation signal is transmitted to each local buffer pool that is sharing the data in the group buffer pool. Additionally, the first computer spawns a second transaction at a second computer that is dependent on the updates to the data that were made by the first transaction. At the second computer, the local buffer pool invalidation signal is received, the local buffer pool is invalidated, and, prior to accessing the local buffer pool, the updated data is copied from the group buffer pool to the local buffer pool.